import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.regex.Pattern;

public class Main {

	private static final String DATABASE_NAME = "spells_core_magic_combat.mp3";
	private static final String TABLE_NAME = "spell_data";

	private static final String CREATE_SPELL_DATA_TABLE = "CREATE TABLE spell_data (_id INTEGER PRIMARY KEY ASC, spell_name TEXT, spell_school TEXT,"
			+ "spell_subschool TEXT, spell_descriptor TEXT, spell_casttime TEXT, spell_components TEXT, spell_range TEXT, spell_area TEXT, spell_target TEXT, "
			+ "spell_duration TEXT, spell_savingthrow TEXT, spell_resistance TEXT, spell_source TEXT, spell_description TEXT, spell_effect TEXT);";

	private static final String CREATE_CLASS_NAMES_TABLE = "CREATE TABLE class_names ("
			+ "_id INTEGER PRIMARY KEY ASC," + "class_name TEXT);";

	private static final String CREATE_METAMAGIC_TABLE = " CREATE TABLE metamagic ("
			+ "_id INTEGER PRIMARY KEY ASC,"
			+ "meta_name TEXT, "
			+ "meta_adjust INTEGER,"
			+ "meta_prereq TEXT,"
			+ "meta_benefit TEXT," + "meta_source TEXT" + ");";

	private static final String CREATE_SPELL_LVLS_TABLE = ""
			+ "CREATE TABLE spell_lvls (_id INTEGER PRIMARY KEY ASC, "
			+ "spell_id INTEGER," + "spell_lvl INTEGER," + "class_id INTEGER,"
			+ "FOREIGN KEY(spell_id) REFERENCES spell_data(_id),"
			+ "FOREIGN KEY(class_id) REFERENCES class_names(_id))";

	// private static final String METADATA =
	// "CREATE TABLE android_metadata (locale TEXT DEFAULT 'en_US')";

	static HashMap<String, Integer> columns;
	static HashMap<String, Integer> class_map;
	static ArrayList<String> classnames;
	static ArrayList<Pair<String, Integer>> metamagics;
	static int class_id_index = 0;
	static int spell_lvl_index = 0;

	public static void main(String[] args) throws Exception {

		class_map = new HashMap<String, Integer>();
		classnames = new ArrayList<String>();
		metamagics = new ArrayList<Pair<String, Integer>>();
		columns = new HashMap<String, Integer>();
		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:"
				+ DATABASE_NAME);
		Statement stat = conn.createStatement();
		Statement stat2 = conn.createStatement();
		stat.executeUpdate("drop table if exists character_spells");
		stat.executeUpdate("drop table if exists spell_data;");
		stat.executeUpdate("drop table if exists class_names;");
		stat.executeUpdate("drop table if exists spell_lvls;");
		stat.executeUpdate("drop table if exists metamagic;");
		// stat.executeUpdate("drop table if exists android_metadata;");
		stat.executeUpdate(CREATE_SPELL_DATA_TABLE);
		stat.executeUpdate(CREATE_CLASS_NAMES_TABLE);
		stat.executeUpdate(CREATE_SPELL_LVLS_TABLE);
		stat.executeUpdate(CREATE_METAMAGIC_TABLE);
		// stat.executeUpdate(METADATA);

		PreparedStatement prepSpellData = conn
				.prepareStatement("insert into spell_data values (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?);");

		PreparedStatement prepClassNames = conn
				.prepareStatement("insert into class_names values (?, ?);");

		PreparedStatement prepSpellLvls = conn
				.prepareStatement("insert into spell_lvls values (?, ?, ?, ?);");

		PreparedStatement prepMetamagic = conn
				.prepareStatement("insert into metamagic values(?, ?, ?, ?, ?, ?)");

		InputStream is = new FileInputStream("spell_full_combat.csv");
		BufferedReader in = new BufferedReader(new InputStreamReader(is));
		String s;
		int line_nr = 0;
		int spell_id = 0;
		ArrayList<Pair<String, Integer>> lvlPairList = new ArrayList<Pair<String, Integer> >();
		while ((s = in.readLine()) != null && s.length() > 0) {

			// Split line.
			String[] tokens = s.split("\t");
			String tmp;

			// Add all columns to map. 
			if (line_nr == 0) {
				for (int j = 0; j < tokens.length; j++) {
					tmp = tokens[j].substring(1, tokens[j].length() - 1);
					columns.put(tmp, j);
				}

				line_nr++;
				continue; // Nothing else to do with this first line.
			}

			/*
			 * CREATE TABLE spell_data ( spell_id INTEGER PRIMARY KEY ASC,
			 * spell_name TEXT, spell_school TEXT, "spell_subschool TEXT,
			 * spell_descriptor TEXT, spell_casttime TEXT, spell_components
			 * TEXT, spell_range TEXT, spell_area TEXT, spell_target TEXT,
			 * spell_duration TEXT, spell_savingthrow TEXT, spell_resistance
			 * TEXT, spell_description TEXT, spell_effect TEXT);" ;
			 */

			prepSpellData.setInt(1, spell_id); // ID
			int col_nr = 2;

			int idx = columns.get("name");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // name

			// Spell levels.

			Pattern pattern = Pattern.compile("[0-9]+");
			idx = columns.get("spell_level");
			tmp = parseData(tokens[idx]);

			if (tmp != null && tmp.length() > 0) {
				String[] lvltokens = tmp.split(",");

				for (int i = 0; i < lvltokens.length; i++) {
					lvltokens[i] = lvltokens[i].trim().toUpperCase();
					
					// Remove RED MANTIS ASSASSIN class. 
					if(!lvltokens[i].equals("RED MANTIS ASSASSIN 2"))
					{
					
					String lvlToken = lvltokens[i];
					
					String[] t = lvlToken.split("/");
					
					// Not a shared lvl by way of /
					if(t.length == 1)
					{
						t = t[0].split(" ");
						int lvl = -1;
						lvl = Integer.parseInt(t[1]);
						lvlPairList.add(new Pair<String, Integer>(t[0], lvl));
						
					}else	// Shared lvl. 
					{
						
						String ttrimmed = t[t.length-1].trim();
						int lvlIdx = ttrimmed.lastIndexOf(' ');
						int lvl = Integer.parseInt(ttrimmed.substring(lvlIdx).trim());
						
						// Go through each class that shares lvl. 
						for(int j = 0; j < t.length; j++)
						{
							String[] tt = t[j].trim().split(" ");
							lvlPairList.add(new Pair<String, Integer>(tt[0], lvl));
							
							
						}
						
					}
					
					// Go through list of lvl pairs and add them. Most often only 1 or 2. 
					for (int j = 0; j < lvlPairList.size(); j++) {
						Pair<String, Integer> p = lvlPairList.get(j);
						
						// Add Class Names
						if (!class_map.containsKey(p.first)) {
							class_map.put(p.first, class_id_index);
							classnames.add(p.first);
							prepClassNames.setInt(1, class_id_index++);
							prepClassNames.setString(2, p.first);
							prepClassNames.addBatch();
						}

						prepSpellLvls.setInt(1, spell_lvl_index++);
						prepSpellLvls.setInt(2, spell_id); // Spell Id
						prepSpellLvls.setInt(3, p.second); // Spell Lvl
						prepSpellLvls.setInt(4, class_map.get(p.first)); // Class
																			// Id
						prepSpellLvls.addBatch();
					}
					lvlPairList.clear();
					}
				}
			}

			idx = columns.get("school");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // school

			idx = columns.get("subschool");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // subschool

			idx = columns.get("descriptor");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // descriptor

			idx = columns.get("casting_time");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // casting_time

			idx = columns.get("components");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // components

			idx = columns.get("range");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // range

			idx = columns.get("area");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // area

			idx = columns.get("targets");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // targets

			idx = columns.get("duration");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // duration

			idx = columns.get("saving_throw");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // saving_throw

			idx = columns.get("spell_resistence");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // spell_resistence

			idx = columns.get("source");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // source
			
			idx = columns.get("description");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // description

			idx = columns.get("effect");
			tmp = parseData(tokens[idx]);
			prepSpellData.setString(col_nr++, tmp); // effect

			prepSpellData.addBatch();

			spell_id++;

		}
		in.close();

		// Add metamagic stuff.
		is = new FileInputStream("metamagic.txt");
		in = new BufferedReader(new InputStreamReader(is));

		int metamagic_idx = 0;
		while ((s = in.readLine()) != null && s.length() > 0) {

			// Index
			prepMetamagic.setInt(1, metamagic_idx++);

			String[] tokens = s.split("\t");

			// Name
			String name = tokens[0];
			int idx = name.indexOf("Spell");
			if (idx != -1) {
				name = name.substring(0, idx);
			}
			name = name.trim();
			prepMetamagic.setString(2, name);

			// Lvl Adjustment
			String adjStr = tokens[2];

			adjStr = (adjStr.charAt(0) == '+') ? adjStr.substring(1) : adjStr;

			if (adjStr.contains("Special")) {
				adjStr = "0";
			}

			int adj = Integer.parseInt(adjStr.trim());
			prepMetamagic.setInt(3, adj);

			// Prereq
			String prereq = tokens[3].trim();
			if (prereq.equals("-")) {
				prereq = "";
			}
			prepMetamagic.setString(4, prereq);

			// Benefit

			String benefit = tokens[4].trim();
			prepMetamagic.setString(5, benefit);

			// Source
			String source = tokens[5].trim();
			prepMetamagic.setString(6, source);

			prepMetamagic.addBatch();
		}

		conn.setAutoCommit(false);
		prepSpellData.executeBatch();
		prepClassNames.executeBatch();
		prepSpellLvls.executeBatch();
		prepMetamagic.executeBatch();
		conn.setAutoCommit(true);

		// Statement metastatement = conn.createStatement();
		// metastatement.executeUpdate("INSERT INTO android_metadata VALUES ('en_US');");

		/*
		 * spell_id INTEGER PRIMARY KEY ASC, spell_name TEXT, spell_school TEXT,
		 * "spell_subschool TEXT, spell_descriptor TEXT, spell_casttime TEXT,
		 * spell_components TEXT, spell_range TEXT, spell_area TEXT,
		 * spell_target TEXT, spell_duration TEXT, spell_savingthrow TEXT,
		 * spell_resistance TEXT, spell_description TEXT, spell_effect TEXT);"
		 */
		ResultSet rs = stat.executeQuery("select * from spell_data;");

		int i = 0;
		while (rs.next() && i < 10) {
			int id = rs.getInt("_id");
			System.out.println("spell_id = " + id);
			System.out.println("spell_name = " + rs.getString("spell_name"));
			System.out
					.println("spell_school = " + rs.getString("spell_school"));
			System.out.println("spell_subschool = "
					+ rs.getString("spell_subschool"));
			System.out.println("spell_descriptor = "
					+ rs.getString("spell_descriptor"));
			System.out.println("spell_casttime = "
					+ rs.getString("spell_casttime"));
			System.out.println("spell_components = "
					+ rs.getString("spell_components"));
			System.out.println("spell_range = " + rs.getString("spell_range"));
			System.out.println("spell_area = " + rs.getString("spell_area"));
			System.out
					.println("spell_target = " + rs.getString("spell_target"));
			System.out.println("spell_duration = "
					+ rs.getString("spell_duration"));
			System.out.println("spell_savingthrow = "
					+ rs.getString("spell_savingthrow"));
			System.out.println("spell_resistance = "
					+ rs.getString("spell_resistance"));
			System.out.println("spell_description = "
					+ rs.getString("spell_description"));
			System.out
					.println("spell_effect = " + rs.getString("spell_effect"));

			ResultSet rss = stat2
					.executeQuery("select _id, spell_id, spell_lvl, class_id from spell_lvls where spell_id = "
							+ id + ";");
			System.out.println("\tspell_levels");
			while (rss.next()) {
				System.out.println("\t" + classnames.get(rss.getInt(4)) + " - "
						+ rss.getInt(3));
			}
			rss.close();
			System.out.println();
			i++;
		}
		rs.close();

		conn.close();
	}

	public static String parseData(String str) {
		if (str == null) {
			return "";
		}

		if (!str.equals("") && str.contains("\"")) // Not a number
		{
			str = str.substring(1, str.length() - 1);
			if (!str.contains("www")) { // it's no web address
				return str;
			} else { // it's a web address, we need the name of the page only.
				int idx = str.lastIndexOf('/');
				String substr = str.substring(idx + 1);
				String[] tokens = substr.split("-");
				StringBuilder sb = new StringBuilder();
				for (int i = 0; i < tokens.length; i++) {
					String s = tokens[i];
					String ss = s.substring(0, 1);
					sb.append(ss.toUpperCase());
					sb.append(s.substring(1));
					if (i < tokens.length - 1) {
						sb.append(" ");
					}
				}
				return sb.toString();
			}
		} else { // Number or null.
			return str;
		}
	}

}
